﻿CREATE PROCEDURE [dbo].[proc_Product_SalesStats_GetItem]
(
	@ProductId INT,
	@sCompanyId VARCHAR(300),
	@NOE_Flag VARCHAR(10),
	@TypeId VARCHAR(10),
	@StartDate nvarchar(10),
	@EndDate nvarchar(10),
	@StartIndex int,
	@EndIndex int,
	@DeptId int
)
AS
BEGIN
	Declare @cSql VarChar(4000),@Where1 VarChar(500)

	--指定日期
	Set @Where1 = 'CreateDate>=@sRq	And CreateDate<=@eRq'
	
	If @sCompanyId <> '0'	--指定公司
		Set @Where1 = @Where1 + ' And CompanyId In(' + @sCompanyId + ')'

	If @DeptId <>0	--指定部门
	Begin
		Set @Where1=@Where1 +' And deptid in(Select id From Department Where companyid='+@sCompanyId+' And Left(bmbh,Len(@dep))=@dep)'
	End

	If @NOE_Flag <> '100'	--指定成单类别(常规、非常规、非企)
		Set @Where1 = @Where1 + ' And NOE_Flag=' + @NOE_Flag

	If @TypeId <> '0'	--指定成单类型(新增、续费)
		Set @Where1 = @Where1 + ' And TypeId=' + @TypeId

	--指定产品
	Set @Where1 = @Where1 + ' And ProductId='+Convert(varchar(10),@ProductId)
	
	SET @cSql='
	Declare @sRq Datetime,@eRq Datetime,@dep varchar(30)
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
	'
	
	If @DeptId <>0	--指定部门
		Set @cSql = @cSql + 'Select @dep=bmbh From Department Where companyid='+@sCompanyId+' And id='+Convert(varchar,@DeptId)

	Set @cSql = @cSql + '
	;WITH list As (Select ROW_NUMBER() OVER (ORDER BY CreateDate) AS Row,
		id,
		CusName,
		OrderId,
		SalePrice,
		CostPrice,
		NOE_Flag,
		IsFinish,
		CreateDate,
		IsHedge As Orderids
		From Project 
		Where ' + @Where1 + '
	)
	Select *,
	(Select Count(*) From list) As RecodrCount ,
	(Select Sum(SalePrice) From list) As SalePriceCount,
	(Select Sum(CostPrice) From list) As CostPriceCount
	From list
	Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex)+'
	ORDER BY Row'

	EXEC (@cSql)
	Print(@cSql)
END